package game.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

/**
 * JDBC相关的工具类
 * @author WangshMac
 */
public class DBUtil
{
	private static String driver = "com.mysql.jdbc.Driver" ; 
	private String url = "jdbc:mysql:///game?useSSL=false" ; 
	private String user = "root";
	private String password = "123456"; 
	
	/* 加载驱动只执行一次 */
	static
	{
		try
		{
			/* 加载驱动 */
			Class.forName(driver);
		} catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		}
	}
	
	/**
	 * 获取一个Connection
	 * @return
	 */
	public Connection getConnection()
	{
		try
		{
			/* 打开链接 */
			Connection connection = DriverManager.getConnection(url, user, password);
			return connection ;
		} catch (SQLException e)
		{
			e.printStackTrace();
		} 
		return null ; 
	}
	
	/**
	 * 关闭水管
	 */
	public void closeAll(Connection connection,Statement st,ResultSet rs)
	{
		/* 关闭水管 */
		try
		{
			if(rs != null)
			{
				rs.close();
				rs = null ; 
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		try
		{
			if(st != null)
			{
				st.close(); 
				st = null ; 
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		
		try
		{
			if(connection != null)
			{
				connection.close(); 
				connection = null ; 
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args)
	{
		/*standardJDBC();*/
		String sql = "select * from a_demo;" ; 
		
		DBUtil dbUtil = new DBUtil() ; 
		/* 获取一个链接 */
		Connection connection = dbUtil.getConnection() ; 
		PreparedStatement pst = null ;
		ResultSet rs = null ; 
		try
		{
			/* 创建sql语句 */
			pst = connection.prepareStatement(sql);
			rs = pst.executeQuery() ; 
			System.out.println("id\tname\tcontent\tstatus\tcreateTime\tupdateTime\tpubtime");
			while(rs.next())
			{
				String id = rs.getString("id");
				String name = rs.getString("name");
				String content = rs.getString("content");
				byte status = rs.getByte("status");
				Date createTime = rs.getTimestamp("createTime");
				Date updateTime = rs.getTimestamp("updateTime");
				Date pubTime = rs.getTime("pubTime");
				System.out.println(id+"\t"+ name +"\t"+ content +"\t"+ status +"\t"+ 
				createTime.toLocaleString() +"\t"+ updateTime.toLocaleString() +"\t" + pubTime.toLocaleString());
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			dbUtil.closeAll(connection, pst, rs);
		}
	}

	/**
	 * 
	 * 第一步和第二步,放到一个方法中
	 * 第四步放到一个方法中
	 * @param args
	 */
	private static void standardJDBC()
	{
		String driver = "com.mysql.jdbc.Driver" ; 
		String url = "jdbc:mysql:///game?useSSL=false" ; 
		String user = "root";
		String password = "123456"; 
		String sql = "select * from a_demo ; "; 

		Connection connection = null ; 
		Statement st = null ;
		ResultSet rs = null ;
		try
		{
			/* 加载驱动 */
			Class.forName(driver);
			/* 打开链接 */
			connection = DriverManager.getConnection(url, user, password);
			/* 操作数据库 */
			st = connection.createStatement();
			rs = st.executeQuery(sql);
			System.out.println("id\tname\tcontent\tstatus\tcreateTime\tupdateTime\tpubtime");
			while(rs.next())
			{
				String id = rs.getString("id");
				String name = rs.getString("name");
				String content = rs.getString("content");
				byte status = rs.getByte("status");
				Date createTime = rs.getTimestamp("createTime");
				Date updateTime = rs.getTimestamp("updateTime");
				Date pubTime = rs.getTime("pubTime");
				System.out.println(id+"\t"+ name +"\t"+ content +"\t"+ status +"\t"+ 
				createTime.toLocaleString() +"\t"+ updateTime.toLocaleString() +"\t" + pubTime.toLocaleString());
			}
		} catch (ClassNotFoundException e)
		{
			e.printStackTrace();
			System.out.println("==加载驱动失败==");
		} catch (SQLException e)
		{
			e.printStackTrace();
			System.out.println("==操作数据库失败==");
		} finally
		{
			/* 关闭水管 */
			try
			{
				if(rs != null)
				{
					rs.close();
					rs = null ; 
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
			try
			{
				if(st != null)
				{
					st.close(); 
					st = null ; 
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
			
			try
			{
				if(connection != null)
				{
					connection.close(); 
					connection = null ; 
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
	}
}